package cn.com.shopec.erp.product.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import cn.com.shopec.erp.common.orm.impl.HibernateEntityDao;
import cn.com.shopec.erp.common.page.PageFinder;
import cn.com.shopec.erp.common.page.PageQuery;
import cn.com.shopec.erp.common.utils.StringUtil;
import cn.com.shopec.erp.product.dao.IProductStockDao;
import cn.com.shopec.erp.product.entity.ProductStock;
import cn.com.shopec.erp.product.entity.ProductStockExt;
import cn.com.shopec.erp.product.vo.ProductStockVo;

@Repository
public class ProductStockDaoImpl extends HibernateEntityDao<ProductStock> implements IProductStockDao {

	@SuppressWarnings("unchecked")
	@Override
	public PageFinder<ProductStockExt> productStockPaged(ProductStockVo productStockVo, PageQuery pageQuery) {
		String params = "";
		if (StringUtil.isExist(productStockVo.getWhNo())) {
			params += " and ps.WH_NO='" + productStockVo.getWhNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getSupNo())) {
			params += " and ps.SUP_NO='" + productStockVo.getSupNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getProdNo())) {
			params += " and ps.PROD_NO='" + productStockVo.getProdNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getCommoNo())) {
			params += " and ps.COMMO_NO='" + productStockVo.getCommoNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getCatNo())) {
			params += " and cc.CAT_NO='" + productStockVo.getCatNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getCatName())) {
			params += " and cc.CAT_NAME '%" + productStockVo.getCatName() + "%'";
		}
		if (StringUtil.isExist(productStockVo.getBrandNo())) {
			params += " and b.BRAND_NO='" + productStockVo.getBrandNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getBrandName())) {
			params += " and b.BRAND_NAME '%" + productStockVo.getBrandName() + "%'";
		}
		if (StringUtil.isExist(productStockVo.getWhName())) {
			params += " and w.WH_NAME like '%" + productStockVo.getWhName() + "%'";
		}
		if (StringUtil.isExist(productStockVo.getSupName())) {
			params += " and si.SUP_NAME like '%" + productStockVo.getSupName() + "%'";
		}
		if (StringUtil.isExist(productStockVo.getCommoName())) {
			params += " and c.COMMO_NAME like '%" + productStockVo.getCommoName() + "%'";
		}
		String sql = "from tbl_product_stock ps " + "left join tbl_commodity c on ps.commo_no=c.commo_no "
				+ "left join tbl_commodity_cat cc on c.cat_no=cc.cat_no "
				+ "left join tbl_brand b on c.brand_no=b.brand_no "
				+ "left join tbl_supplier_info si on ps.sup_no=si.code "
				+ "LEFT join tbl_prod_spec_value psv on psv.prod_no = ps.prod_no "
				+ "LEFT join tbl_spec_val_option svo on svo.option_no = psv.val_option_no "
				+ "LEFT join tbl_warehouse w on ps.wh_no = w.wh_no where 1=1 " + params;

		String countsql = "select count(*) " + sql;
		/*
		 * String querysql =
		 * "select ps.id,ps.wh_no,ps.sup_no,ps.prod_no,ps.commo_no,ps.product_total,"
		 * +
		 * "ps.qualified_quantity,ps.defective_quantity,ps.update_time,ps.pre_occupy_quantity,c.commo_name,cc.cat_no, "
		 * +
		 * "cc.cat_name,b.brand_name,b.brand_no,w.wh_name,si.sup_name,ps.po_no,"
		 * + "(select svo.option_name from tbl_spec_val_option svo " +
		 * "left outer join tbl_prod_spec_value psv " +
		 * "on svo.option_no=psv.val_option_no where psv.prod_no=ps.prod_no) spec_name "
		 * + sql +
		 * " order by w.wh_name,si.sup_name,ps.product_total,ps.update_time DESC"
		 * ;
		 */
		String querysql = "select ps.id,ps.wh_no,ps.sup_no,ps.prod_no,ps.commo_no,ps.product_total,"
				+ "ps.qualified_quantity,ps.defective_quantity,ps.update_time,ps.pre_occupy_quantity,c.commo_name,cc.cat_no, "
				+ "cc.cat_name,b.brand_name,b.brand_no,w.wh_name,si.sup_name,ps.po_no,ps.vip_occupy_quantity,"
				+ "GROUP_CONCAT(SVO.OPTION_NAME) spec_name " + sql
				+ " group by ps.id order by w.wh_name,si.sup_name,ps.product_total,ps.update_time DESC";

		BigInteger count = (BigInteger) this.createSQLQuery(countsql).list().get(0);
		List<ProductStockExt> list = new ArrayList<ProductStockExt>();
		PageFinder<ProductStockExt> page = null;
		if (pageQuery != null) {
			list = (List<ProductStockExt>) getSession().createSQLQuery(querysql).addEntity(ProductStockExt.class)
					.setFirstResult((pageQuery.getPage() - 1) * pageQuery.getPageSize())
					.setMaxResults(pageQuery.getPageSize()).list();
			page = new PageFinder<ProductStockExt>(pageQuery.getPage(), pageQuery.getPageSize(), count.intValue(),
					list);
		} else {
			list = (List<ProductStockExt>) getSession().createSQLQuery(querysql).addEntity(ProductStockExt.class)
					.list();
			page = new PageFinder<ProductStockExt>(1, count.intValue() == 0 ? 1 : count.intValue(), count.intValue(),
					list);
		}
		return page;
	}

	/***
	 * 获取次品信息
	 */
	@Override
	public PageFinder<ProductStockExt> productStockDefect(ProductStockVo productStockVo, PageQuery pageQuery) {

		String sql = "from tbl_product_stock ps " + "left join tbl_commodity c on ps.commo_no=c.commo_no "
				+ "left join tbl_commodity_cat cc on c.cat_no=cc.cat_no "
				+ "left join tbl_brand b on c.brand_no=b.brand_no "
				+ "left join tbl_supplier_info si on ps.sup_no=si.code "
				+ "LEFT join tbl_prod_spec_value psv on psv.prod_no = ps.prod_no "
				+ "LEFT join tbl_spec_val_option svo on svo.option_no = psv.val_option_no "
				+ "left join tbl_warehouse w on ps.wh_no=w.wh_no WHERE ps.defective_Quantity >0 group by ps.id";
		String countsql = "select count(*) " + sql;
		String querysql = "select ps.id," + "ps.wh_no," + "ps.vip_occupy_quantity," + "ps.sup_no," + "ps.prod_no,"
				+ "ps.commo_no," + "ps.product_total," + "ps.qualified_quantity," + "ps.defective_quantity,"
				+ "ps.update_time," + "ps.pre_occupy_quantity," + "c.commo_name," + "cc.cat_no, " + "cc.cat_name,"
				+ "b.brand_name," + "b.brand_no," + "w.wh_name," + "si.sup_name," + "ps.po_no,"
				+ "GROUP_CONCAT(SVO.OPTION_NAME) spec_name " + sql;

		BigInteger count = (BigInteger) this.createSQLQuery(countsql).list().get(0);

		List<ProductStockExt> list = (List<ProductStockExt>) getSession().createSQLQuery(querysql)
				.addEntity(ProductStockExt.class).setFirstResult((pageQuery.getPage() - 1) * pageQuery.getPageSize())
				.setMaxResults(pageQuery.getPageSize()).list();
		PageFinder<ProductStockExt> page = new PageFinder<ProductStockExt>(pageQuery.getPage(), pageQuery.getPageSize(),
				count.intValue(), list);
		return page;
	}

	/**
	 * 查询指定的仓库，供货商，货品，商品的对象（唯一）
	 * 
	 * @param productStock
	 * @return
	 */
	@Override
	public ProductStock findUniqueProduct(ProductStock productStock) {
		DetachedCriteria detachedCriteria = createCriteria();
		detachedCriteria.add(Restrictions.eq("whNo", productStock.getWhNo()));
		detachedCriteria.add(Restrictions.eq("prodNo", productStock.getProdNo()));
		detachedCriteria.add(Restrictions.eq("supNo", productStock.getSupNo()));
		detachedCriteria.add(Restrictions.eq("commoNo", productStock.getCommoNo()));
		List<ProductStock> poageFinder = findByCriteria(detachedCriteria);
		if (!CollectionUtils.isEmpty(poageFinder)) {
			return poageFinder.get(0);
		}
		return null;
	}

	@Override
	public List<Map<String, Object>> productStockList(ProductStockVo productStockVo) {
		String params = "";
		if (StringUtil.isExist(productStockVo.getWhNo())) {
			params += " and ps.WH_NO='" + productStockVo.getWhNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getSupNo())) {
			params += " and ps.SUP_NO='" + productStockVo.getSupNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getProdNo())) {
			params += " and ps.PROD_NO='" + productStockVo.getProdNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getCommoNo())) {
			params += " and ps.COMMO_NO='" + productStockVo.getCommoNo() + "'";
		}

		String sql = "select sum(IFNULL(ps.qualified_quantity,0)-IFNULL(ps.pre_occupy_quantity,0)) qualifiedQuantity,"
				+ " ps.pre_occupy_quantity preOccupyQuantity,ps.wh_no whNo " + " from tbl_product_stock ps "
				+ " left join tbl_warehouse w on ps.wh_no=w.wh_no where w.delete_flag=1 " + params
				+ " group by ps.wh_no ";
		List<Map<String, Object>> list = getSession().createSQLQuery(sql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		return list;
	}

	@Override
	public Long getProductStockSum(ProductStockVo productStockVo) {
		String params = "";
		if (StringUtil.isExist(productStockVo.getWhNo())) {
			params += " and ps.WH_NO='" + productStockVo.getWhNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getSupNo())) {
			params += " and ps.SUP_NO='" + productStockVo.getSupNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getProdNo())) {
			params += " and ps.PROD_NO='" + productStockVo.getProdNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getCommoNo())) {
			params += " and ps.COMMO_NO='" + productStockVo.getCommoNo() + "'";
		}

		String sql = "select sum(ps.qualified_quantity) " + " from tbl_product_stock ps "
				+ " left join tbl_warehouse w on ps.wh_no=w.wh_no " + " where w.delete_flag=1 " + params
				+ " group by ps.wh_no ";
		List list = getSession().createSQLQuery(sql).list();
		if (list != null && !list.isEmpty()) {
			return Long.valueOf(list.get(0).toString());
		}
		return null;
	}

	@Override
	public List<ProductStock> getProductStockList(ProductStockVo productStockVo) {
		String params = "";
		if (StringUtil.isExist(productStockVo.getWhNo())) {
			params += " and ps.WH_NO='" + productStockVo.getWhNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getSupNo())) {
			params += " and ps.SUP_NO='" + productStockVo.getSupNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getProdNo())) {
			params += " and ps.PROD_NO='" + productStockVo.getProdNo() + "'";
		}
		if (StringUtil.isExist(productStockVo.getCommoNo())) {
			params += " and ps.COMMO_NO='" + productStockVo.getCommoNo() + "'";
		}

		String sql = "select ps.id,ps.wh_no,ps.sup_no,ps.prod_no,ps.commo_no,ps.product_total,ps.defective_quantity,"
				+ "ps.pre_occupy_quantity,ps.update_time,ps.po_no,ps.vip_occupy_quantity,"
				+ "(IFNULL(ps.qualified_quantity,0)-IFNULL(ps.pre_occupy_quantity,0)) qualified_quantity FROM tbl_product_stock ps WHERE 1=1 "
				+ params + "  ORDER BY qualified_quantity DESC ";
		return getSession().createSQLQuery(sql).addEntity(ProductStock.class).list();
	}

	@Override
	public boolean changeQuantity(String id, Long qualifiedQuantity, long preOccupyQuantity, long defectiveQuantity) {
		String hql = "update ProductStock as p set " 
				+ " p.qualifiedQuantity = qualifiedQuantity - :qualifiedQuantity,"
				+ " p.preOccupyQuantity = preOccupyQuantity - :preOccupyQuantity,"
				+ " p.defectiveQuantity = defectiveQuantity - :defectiveQuantity,"
				+ " p.productTotal = productTotal - :productTotal";

		if(preOccupyQuantity == 0L){
			hql += " where qualifiedQuantity - preOccupyQuantity - :qualifiedQuantity >= 0";
		}else{
			hql += " where qualifiedQuantity - :qualifiedQuantity >= 0";
		}
		hql += " and preOccupyQuantity - :preOccupyQuantity >= 0" 
			 + " and defectiveQuantity - :defectiveQuantity >= 0"
			 + " and p.id = :id";
		int count = getSession().createQuery(hql).setParameter("id", id)
				.setParameter("qualifiedQuantity", qualifiedQuantity)
				.setParameter("preOccupyQuantity", preOccupyQuantity)
				.setParameter("defectiveQuantity", defectiveQuantity)
				.setParameter("productTotal", qualifiedQuantity + defectiveQuantity).executeUpdate();
		return count > 0;
	}

	@Override
	public boolean setVipOccupyQuantity(String id, Long vipOccupyQuantity) {
		String hql = "update ProductStock as p set "
				+ " p.preOccupyQuantity = p.preOccupyQuantity - p.vipOccupyQuantity + :vipOccupyQuantity,"
				+ " p.vipOccupyQuantity = :vipOccupyQuantity"
				+ " where p.preOccupyQuantity - p.vipOccupyQuantity + :vipOccupyQuantity >= 0 "
				+ " and p.preOccupyQuantity - p.vipOccupyQuantity + :vipOccupyQuantity <= p.qualifiedQuantity"
				+ " and p.id = :id";
		int count = getSession().createQuery(hql).setParameter("id", id)
				.setParameter("vipOccupyQuantity", vipOccupyQuantity).executeUpdate();
		return count > 0;
	}

}
